Case Study: 
Creating a Data Base on Linux
[DriftlessHealthCast]
Vermont State University CIS uses Lemuria as a departmental server.
https://lemuria.cis.vermontstate.edu/
PLEASE NOTE: If Lemuria fails, it will be replaced by Zeta: 
https://zeta.cis.vermontstate.edu/
Lemuria is a Linux machine.

Using Lemuria:
See CaseStudyCountS24 for detailed information on 
Access to Lemuria
using FileZilla
using Putty

Creating a PostGRE SQL (PSQL) Database on Lemuria

Quick Reference Guide:
createdb database		//creates a database
dropdb database		//disposes of a databasee
psql database		//use a database
ALTER ROLE iAmAStudent WITH PASSWORD ‘whatever’;
				//associate the password ‘whatever’
				//owned by iAmAStudent
				//with the current database

Now we will attempt to create a more complex,  database, together with a complex web page to manipulate it.  
(It is assumed you already know about Lemuria and SQL)
A good reference for  PostGRESQL commands is:
https://www.postgresql.org/docs/current/sql-commands.html
The database was created:
createdb driftless

and is opened with:
psql ddriftless

A password for the database was set:
ALTER ROLE ktookey WITH PASSWORD 'frenchfry';
//The actual password to the database is different

This is a database about a Podcast, run by my favorite doctor in the entire world.
(I am prejudiced, but it really is a good podcast.)
The podcast mainly deals with Episodes, which have Dates, Titles, and Descriptions. The episodeDescription can be embedded so that there is link to a specific podcast from a web page.

These are the SQL tables in use:
[Please note that since every table has the same primary key, it would technically not violate normal form to have it all in one table.]

CREATE TABLE episodeTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeDate CHAR(10),
	episodeTitle CHAR(80)
);
CREATE TABLE descriptionTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeDescription CHAR(500)
);
CREATE TABLE linkTable(
	episodeID CHAR(10) PRIMARY KEY,
	episodeLink CHAR(500)
);

Each new episode must be inserted into the database:

INSERT INTO episodeTable VALUES
	('17666918', '01/24/2021', 'Bananas: Myth Busting');   

INSERT INTO descriptionTable VALUES
	('17666918', 'I hear a lot of my friends who strictly watch their diet that they avoid bananas out of concern for weight loss. I also hear a lot of my diabetic patients are worried about having a banana could spike their blood sugars. We take a closer look at some of the macro and micronutrients in bananas and how they are metabolized by the body.'); 

INSERT INTO linkTable VALUES
	('17666918', 
'<iframe title="Libsyn Player" style="border: none" 
Src = "//html5-player.libsyn.com/embed/episode/id/17666918/height/90/theme/custom/thumbnail/yes/direction/forward/render-playlist/no/custom-color/000000/" 
height="90" width="100%" scrolling="no"  allowfullscreen webkitallowfullscreen mozallowfullscreen oallowfullscreen msallowfullscreen>
</iframe>');

The information for an episode comes from:
https://driftlesshealthcast.libsyn.com/

By Doctor’s Orders, people need to be able to search for a podcast by:
Topic
Date
Title (word or phase)
Description (word or phase)
Key word (in title or description)

So, I need SQL to do some of this stuff. SAMPLES:
SELECT * FROM episodeTable WHERE POSITION('Banana' IN episodeTitle)>0;

SELECT * FROM episodetable, descriptionTable, linkTable WHERE POSITION('Banana' IN episodetable.episodetitle)>=0;

Once I was sure I had the SQL right, I was ready to look at the PHP.

PHP code can have parameters passed to it, which is often done by using FORMS.
http://lemuria.cis.vtc.edu/~ktookey/driftless/titleSearch.php?partOfTitle=banana

If you append a ‘?’ to a PHP link, it assumes you have parameters.
After the question mark, you list the parameter being passed down, and its value.

(If there are multiple parameters, use an ‘&’ between them.)
http://lemuria.cis.vtc.edu/~ktookey/approve.php?newDiscipline=CIS&newID=9999&newTitle=Game+Design

You can ‘retrieve’ parameter value within PHP as follows:
$newPartOfTitle = $_GET['partOfTitle'];
[Given the line above, this makes $newPartOfTitle get the value ‘banana’.]

If you want the word ‘banana’ to be embedded in quotes, use:
'{$newPartOfTitle}'
(I ALWAYS echo the query to make sure it came out the way I expected.)

See example descriptionSearch.php on next page.

<?php
$servername = "localhost";
$username   = "ktookey";
$password   = "frenchfry";//not the real password
$dbname     = "driftless";

$newPartOfTitle = $_GET['partOfTitle'];
//print "\nPart Of Title:{$newPartOfTitle}<br/>";

$query      = "SELECT episodedate, episodetitle, episodelink FROM episodetable natural join descriptionTable natural join linktable WHERE POSITION('{$newPartOfTitle}' IN descriptiontable.episodeDescription)>0;";
//echo $query;

try {
    $connection = new PDO("pgsql:host=$servername;dbname=$dbname","$username","$password");
    echo "<br/>Searching in podcast titles:<br/>";

    foreach($connection->query($query) as $row) {
	print_r($row["episodedate"]);
	print_r($row["episodetitle"]);
	echo "<br/>";
	print_r($row["episodelink"]);
    }
    echo "End of list.<br/>";
//I say ‘end of list’ so that if nothing is found, something is still printed
    $connection = null;
}
catch (PDOException $e) {
    print "Error: " . $e->getMessage() . "<br/>";
    die();
}
?>

<!—this HTML code has a simple form which calls descriptionSearch.php-->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset">
<html>
<body>
Type part of the description you are looking for:<br/>
<strong>(Spelling must be EXACT!)</strong><br/>
 <div id="formstuff">
 <form  method="get" action="http://lemuria.cis.vtc.edu/~ktookey/driftless/descriptionSearch.php">
                        <label for="whatzit">Part of Description:</label><br>
                        <textarea name="partOfTitle"id="disc" cols="20">
</textarea><br>
                        <input type="submit">
                    </form>
Archives are available.
</div>
</body>
</html>

If you are interested, you may start with 
http://lemuria.cis.vtc.edu/~ktookey/driftless/healthcast4.htm
and explore.
    <h2>Searching</h2>
    <a href= "indexMenu10.htm">Archives-(episodes by date)</a><br/>
    <a href= "button3.htm">Topic Search</a><br/>
    <a href= "titleSearch.htm">Title Search</a><br/>
		titleSearch.php accesses database
    <a href= "descriptionSearch.htm">Description Search</a><br/>
		descriptionSearch.php accesses database
    <a href= "wordy3.htm">Key Word Search</a><br/>
		Search4.php accesses the database

The Database class is not about fancy HTML coding.
But you need to be aware of how a form calls PHP which manipulates the database.